package com.zrentech.order.data.dbhelper;

import java.util.List;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.zrentech.order.data.busibean.CustomerFeedbackBean;
import com.zrentech.order.data.busibean.FoodBean;
import com.zrentech.order.data.busibean.FoodImageBean;
import com.zrentech.order.data.busibean.FoodTypeBean;
import com.zrentech.order.data.busibean.OrderBean;
import com.zrentech.order.data.busibean.OrderDetailBean;
import com.zrentech.order.data.busibean.TableBean;
import com.zrentech.order.data.busibean.TableTypeBean;
import com.zrentech.order.data.dbhelper.builder.CustomerFeedbackDBuilder;
import com.zrentech.order.data.dbhelper.builder.FoodDBuilder;
import com.zrentech.order.data.dbhelper.builder.FoodImageDBuilder;
import com.zrentech.order.data.dbhelper.builder.OrderDBuilder;
import com.zrentech.order.data.dbhelper.builder.OrderDetailDBuilder;
import com.zrentech.order.util.DateTimeHelper;

public class DatabaseImpl implements Database {

	private static final String TAG = "DatabaseImpl";

	private static final String DB_NAME = "zrentech-order";

	private static final String SQL_INSERTORDERTBL_PREFIX = "insert into ordertbl(orderTime,userID,tableID,personNum,isPay,remark)";
	private static final String SQL_INSERTORDERDETAILTBL_PREFIX = "insert into orderdetailtbl(orderID,menuID,num,remark)";
	private static final String SQL_INSERTMENUTBL_PREFIX = "insert into menutbl(typeID,name,price,remark,rating)";
	private static final String SQL_INSERTFOODIMAGETBL_PREFIX = "insert into menuimagetbl(foodID,name,imagePath)";
	private static final String SQL_INSERTCUSTOMERFEEDBACKTBL_PREFIX = "insert into customerfeedbacktbl(foodID,comments,grade)";
	private static final String SQL_INSERTMENUTYPETBL_PREFIX = "insert into menutypetbl(id,name,rating)";
	private static final String SQL_INSERTTABLETBL_PREFIX = "insert into tabletbl(id,typeid,number,name,status,desc)";
	private static final String SQL_INSERTTABLETYPETBL_PREFIX = "insert into tabletypetbl(id,name)";
	private static final String SQL_INSERTTABLE_COMMON_MIDDLE = " values(";

	private static final String TABLENAME_MENUTYPETBL = "menutypetbl";
	private static final String TABLENAME_MENUIMAGETBL = "menuimagetbl";
	private static final String TABLENAME_CUSTOMERFEEDBACKTBL = "customerfeedbacktbl";
	private static final String TABLENAME_MENUTBL = "menutbl";
	private static final String TABLENAME_TABLETYPETBL = "tabletypetbl";
	private static final String TABLENAME_TABLETBL = "tabletbl";
	private static final String TABLENAME_ORDERTBL = "ordertbl";
	private static final String TABLENAME_ORDERDETAILTBL = "orderdetailtbl";

	// 创建菜谱类型表
	private static final String CREATE_TABLE_MENUTYPETBL = " CREATE TABLE 'menutypetbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'name' varchar(20) DEFAULT NULL COMMENT '类型名称',"
			+ " 'rating' double DEFAULT NULL COMMENT '等级',"
			+ " PRIMARY KEY ('id')" + " )";

	// 创建菜谱图片表
	private static final String CREATE_TABLE_MENUIMAGETBL = " CREATE TABLE 'menuimagetbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'foodID' int(11) DEFAULT NULL COMMENT '外键，参照菜谱ID',"
			+ " 'name' varchar(50) DEFAULT NULL COMMENT '图片名称',"
			+ " 'imagePath' varchar(50) DEFAULT NULL COMMENT '图片路径',"
			+ " PRIMARY KEY ('id'),"
			+ " CONSTRAINT 'foreign-key-orderid' FOREIGN KEY ('foodID') REFERENCES 'menutbl' ('id')"
			+ ")";

	// 创建菜谱对应的客户评价表
	private static final String CREATE_TABLE_CUSTOMERFEEDBACKTBL = "CREATE TABLE 'customerfeedbacktbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'foodID' int(11) DEFAULT NULL COMMENT '外键，参照菜谱ID',"
			+ " 'comments' varchar(50) DEFAULT NULL COMMENT '客户评价',"
			+ " 'grade' double DEFAULT NULL COMMENT '打分',"
			+ " PRIMARY KEY ('id'),"
			+ " CONSTRAINT 'foreign-key-orderid' FOREIGN KEY ('foodID') REFERENCES 'menutbl' ('id')"
			+ ")";

	// 创建菜谱表
	private static final String CREATE_TABLE_MENUTBL = " CREATE TABLE 'menutbl' ("
			+ "'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ "'typeID' int(11) DEFAULT NULL COMMENT '外键，引用菜单类型表ID',"
			+ "'name' varchar(50) DEFAULT NULL COMMENT '名称',"
			+ "'price' double DEFAULT NULL COMMENT '价格',"
			+ " 'discount' double DEFAULT NULL COMMENT '折扣',"
			+ "'remark' varchar(200) DEFAULT NULL COMMENT '注备',"
			+ "'rating' double DEFAULT NULL COMMENT '等级',"
			+ " PRIMARY KEY ('id'),"
			+ " KEY 'foreign-key-typeid' ('typeID'),"
			+ " CONSTRAINT 'foreign-key-typeid' FOREIGN KEY ('typeID') REFERENCES 'menutypetbl' ('id')"
			+ " )";

	// 创建台位类型表
	private static final String CREATE_TABLE_TABLETYPETBL = " CREATE TABLE 'tabletypetbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'name' varchar(20) DEFAULT NULL COMMENT '类型名称',"
			+ " PRIMARY KEY ('id')" + " )";

	// 创建台位表
	private static final String CREATE_TABLE_TABLETBL = " CREATE TABLE 'tabletbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'typeid' int(11) DEFAULT NULL COMMENT '台位类型',"
			+ " 'number' int(11) DEFAULT NULL COMMENT '桌号',"
			+ " 'name' varchar(50) DEFAULT NULL COMMENT '名称',"
			+ " 'status' int(11) DEFAULT NULL COMMENT '餐桌状态  0 - 空桌，1 - 有人就餐',"
			+ " 'desc' varchar(200) DEFAULT NULL COMMENT '述描',"
			+ " PRIMARY KEY ('id'),"
			+ " KEY 'foreign-key-typeid' ('typeid'),"
			+ " CONSTRAINT 'foreign-key-typeid' FOREIGN KEY ('typeid') REFERENCES 'tabletypetbl' ('id')"
			+ " )";

	// 创建订单表
	private static final String CREATE_TABLE_ORDERTBL = " CREATE TABLE 'ordertbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'orderTime' varchar(30) DEFAULT NULL COMMENT '下单时间',"
			+ " 'userID' int(11) DEFAULT NULL COMMENT '下单用户',"
			+ " 'tableID' int(11) DEFAULT NULL COMMENT '桌号，外键引用桌号ID',"
			+ " 'personNum' int(11) DEFAULT NULL COMMENT '人数',"
			+ " 'isPay' int(11) DEFAULT '0',"
			+ " 'remark' varchar(200) DEFAULT NULL COMMENT '注备',"
			+ " PRIMARY KEY ('id'),"
			+ " KEY 'foreign-key-tableid' ('tableID'),"
			+ " CONSTRAINT 'foreign-key-tableid' FOREIGN KEY ('tableID') REFERENCES 'tabletbl' ('number')"
			+ " ) ";

	// 创建订单明细表
	private static final String CREATE_TABLE_ORDERDETAILTBL = " CREATE TABLE 'orderdetailtbl' ("
			+ " 'id' int(11) NOT NULL AUTO_INCREMENT,"
			+ " 'orderID' int(11) DEFAULT NULL COMMENT '外键，参照订单ID',"
			+ " 'menuID' int(11) DEFAULT NULL COMMENT '外键，参照菜谱ID',"
			+ " 'num' int(11) DEFAULT NULL COMMENT '数量',"
			+ " 'remark' varchar(200) DEFAULT NULL COMMENT '注备',"
			+ " PRIMARY KEY ('id'),"
			+ " KEY 'foreign-key-orderid' ('orderID'),"
			+ " KEY 'foreign-key-menuid' ('menuID'),"
			+ " CONSTRAINT 'foreign-key-menuid' FOREIGN KEY ('menuID') REFERENCES 'menutbl' ('id'),"
			+ " CONSTRAINT 'foreign-key-orderid' FOREIGN KEY ('orderID') REFERENCES 'ordertbl' ('id')"
			+ ")";

	private Activity mActivity;

	public DatabaseImpl(Activity activity) {
		this.mActivity = activity;
		create();
	}

	private SQLiteDatabase getDb() {
		return mActivity.openOrCreateDatabase(DB_NAME, Context.MODE_PRIVATE,
				null);
	}

	private void create() {
		SQLiteDatabase db = mActivity.openOrCreateDatabase(DB_NAME,
				Context.MODE_PRIVATE, null);

		// 创建必要地表

		db.execSQL(CREATE_TABLE_MENUTYPETBL);
		db.execSQL(CREATE_TABLE_MENUTBL);
		db.execSQL(CREATE_TABLE_MENUIMAGETBL);
		db.execSQL(CREATE_TABLE_CUSTOMERFEEDBACKTBL);
		db.execSQL(CREATE_TABLE_TABLETYPETBL);
		db.execSQL(CREATE_TABLE_TABLETBL);
		db.execSQL(CREATE_TABLE_ORDERTBL);
		db.execSQL(CREATE_TABLE_ORDERDETAILTBL);

		db.close();
	}

	@Override
	/**
	 * 将订单信息加入订单表（明细）
	 */
	public void addDataToOrder(OrderBean order) {
		SQLiteDatabase db = getDb();
		List<OrderDetailBean> orderDetails = order.getOrderDetails();
		try {
			// 查下现有订单表是否有该台位订单记录
			String tableTbl_name = TABLENAME_ORDERTBL;
			String selection = OrderDBuilder.TRACK_TABLEID + "=?";
			String[] selectionArgs = { String.valueOf(order.getTableID()) };
			Cursor c = db.query(tableTbl_name, null, selection, selectionArgs,
					null, null, null);
			if (c.getCount() > 0) {
				// update
				ContentValues values = new ContentValues();
				values.put(OrderDBuilder.TRACK_ID, DateTimeHelper.dateFormat());
				values.put(OrderDBuilder.TRACK_USERID, order.getUserID());
				values.put(OrderDBuilder.TRACK_TABLEID, order.getTableID());
				values.put(OrderDBuilder.TRACK_PERSONNUM, order.getPersonNum());
				values.put(OrderDBuilder.TRACK_ISPAY, order.getIsPay());
				values.put(OrderDBuilder.TRACK_REMARK, order.getRemark());
				db.update(tableTbl_name, values, selection, selectionArgs);
				db.delete(TABLENAME_ORDERDETAILTBL, selection, selectionArgs);
				for (OrderDetailBean detail : orderDetails) {
					insertOrderDetailTbl(detail);
				}
			} else {
				// insert
				String sql = SQL_INSERTORDERTBL_PREFIX
						+ SQL_INSERTTABLE_COMMON_MIDDLE + "'"
						+ DateTimeHelper.dateFormat() + "',"
						+ order.getUserID() + "," + order.getTableID() + ","
						+ order.getPersonNum() + "," + order.getIsPay() + ","
						+ "'" + order.getRemark() + "'" + ")";
				db.execSQL(sql);
				for (OrderDetailBean detail : orderDetails) {
					insertOrderDetailTbl(detail);
				}
			}
		} catch (Exception e) {
			Log.w(TAG,
					"Could not insert the data of order into table named ordertbl: "
							+ e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}

	/**
	 * 插入订单明细表
	 * 
	 * @param db
	 * @param detail
	 */
	public void insertOrderDetailTbl(OrderDetailBean detail) {
		SQLiteDatabase db = getDb();
		String sql = SQL_INSERTORDERDETAILTBL_PREFIX
				+ SQL_INSERTTABLE_COMMON_MIDDLE + detail.getOrderID() + ","
				+ detail.getMenuID() + "," + detail.getNum() + ",'"
				+ detail.getRemark() + "'";
		db.execSQL(sql);
	}

	// 将订单信息从数据库取出(参数id代表订单ID)
	public OrderBean getDataFromOrderTBL(int id) {
		SQLiteDatabase db = getDb();
		OrderBean order = null;
		try {
			String selection = OrderDBuilder.TRACK_ID + "=?";
			String[] selectionArgs = { String.valueOf(id) };
			Cursor c = db.query(TABLENAME_ORDERTBL, null, selection,
					selectionArgs, null, null, null);
			order = new OrderDBuilder().build(c);
			selection = OrderDetailDBuilder.TRACK_ORDERID + "=?";
			c = db.query(TABLENAME_ORDERDETAILTBL, null, selection,
					selectionArgs, null, null, null);
			List<OrderDetailBean> orderDetails = new OrderDetailDBuilder()
					.buildMore(c);
			if (order != null)
				order.setOrderDetails(orderDetails);
			return order;
		} catch (Exception e) {
			Log.w(TAG, "Could get nothing from ordertbl: " + e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
		return order;
	}

	// 更新菜谱信息
	public void updateMenu(FoodBean menu) {
		SQLiteDatabase db = getDb();
		try {
			ContentValues values = new FoodDBuilder().deconstruct(menu);
			String selection = FoodDBuilder.TRACK_ID + "=?";
			String[] selectionArgs = {String.valueOf(menu.getId())};
			db.update(TABLENAME_MENUTBL, values, selection, selectionArgs);
		} catch(Exception e) {
			Log.w(TAG, "Could not update food info: " + e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}
	
	// 删除菜谱信息
	public void delFromMenu(int id) {
		SQLiteDatabase db = getDb();
		try {
			String selection = FoodDBuilder.TRACK_ID + "=?";
			String[] selectionArgs = { String.valueOf(id) };
			db.delete(TABLENAME_MENUTBL, selection, selectionArgs);
			selection = FoodImageDBuilder.TRACK_FOODID + "=?";
			db.delete(TABLENAME_MENUIMAGETBL, selection, selectionArgs);
			selection = CustomerFeedbackDBuilder.TRACK_FOODID + "=?";
			db.delete(TABLENAME_CUSTOMERFEEDBACKTBL, selection, selectionArgs);
		} catch (Exception e) {
			Log.w(TAG, "Could not delete food info: " + e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}	
	}

	// 将订单信息从数据库删除(参数id代表订单ID)
	public void delFromOrder(int id) {
		SQLiteDatabase db = getDb();
		try {
			String selection = OrderDBuilder.TRACK_ID + "=?";
			String[] selectionArgs = { String.valueOf(id) };
			db.delete(TABLENAME_ORDERTBL, selection, selectionArgs);
			selection = OrderDetailDBuilder.TRACK_ORDERID + "=?";
			db.delete(TABLENAME_ORDERDETAILTBL, selection, selectionArgs);
		} catch (Exception e) {
			Log.w(TAG, "Could not delete order info: " + e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}

	// 将菜谱信息加入菜谱表
	public void addDataToMenu(FoodBean menu) {
		SQLiteDatabase db = getDb();
		try {
			String sql = SQL_INSERTMENUTBL_PREFIX
					+ SQL_INSERTTABLE_COMMON_MIDDLE + menu.getTypeID() + ",'"
					+ menu.getName() + "'," + menu.getPrice() + ",'"
					+ menu.getRemark() + "'," + menu.getRating() + ")";
			db.execSQL(sql);
			List<FoodImageBean> foodImages = menu.getFoodImages();
			for (FoodImageBean image : foodImages) {
				addDataToFoodImage(image);
			}
			List<CustomerFeedbackBean> feedBacks = menu.getFeedBacks();
			for (CustomerFeedbackBean feedback : feedBacks) {
				addDataToCustomerFeedback(feedback);
			}
		} catch (Exception e) {
			Log.w(TAG, "Could not insert menu info into db: " + e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}

	// 将菜谱图片信息加入菜谱类型表
	public void addDataToFoodImage(FoodImageBean image) {
		SQLiteDatabase db = getDb();
		String sql = SQL_INSERTFOODIMAGETBL_PREFIX
				+ SQL_INSERTTABLE_COMMON_MIDDLE + image.getFoodID() + ",'"
				+ image.getName() + "','" + image.getImagePath() + "')";
		db.execSQL(sql);
	}

	// 将菜谱对应的用户评价信息加入用户评价表
	public void addDataToCustomerFeedback(CustomerFeedbackBean feedback) {
		SQLiteDatabase db = getDb();
		String sql = SQL_INSERTCUSTOMERFEEDBACKTBL_PREFIX
				+ SQL_INSERTTABLE_COMMON_MIDDLE + feedback.getFoodID() + ",'"
				+ feedback.getComments() + "','" + feedback.getGrade() + "')";
		db.execSQL(sql);
	}

	// 将菜谱类型信息加入菜谱类型表
	public void addDataToMenuType(FoodTypeBean menuType) {
		SQLiteDatabase db = getDb();
		try {
			String sql = SQL_INSERTMENUTYPETBL_PREFIX
					+ SQL_INSERTTABLE_COMMON_MIDDLE + menuType.getId() + ",'"
					+ menuType.getName() + "'," + menuType.getRating() + ")";
			db.execSQL(sql);
		} catch (Exception e) {
			Log.w(TAG,
					"Could not insert menu type info into db: "
							+ e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}

	// 将台位信息加入台位表
	public void addDataToTable(TableBean table) {
		SQLiteDatabase db = getDb();
		try {
			String sql = SQL_INSERTTABLETBL_PREFIX
					+ SQL_INSERTTABLE_COMMON_MIDDLE + table.getId() + ","
					+ table.getTypeid() + "," + table.getNumber() + ",'"
					+ table.getName() + "'," + table.getStatus() + ",'"
					+ table.getDesc() + "'" + ")";
			db.execSQL(sql);
		} catch (Exception e) {
			Log.w(TAG, "Could not insert table info into db: " + e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}

	// 将台位类型信息加入台位类型表
	public void addDataToTableType(TableTypeBean tableType) {
		SQLiteDatabase db = getDb();
		try {
			String sql = SQL_INSERTTABLETYPETBL_PREFIX
					+ SQL_INSERTTABLE_COMMON_MIDDLE + tableType.getId() + ","
					+ tableType.getName() + ")";
			db.execSQL(sql);
		} catch (Exception e) {
			Log.w(TAG,
					"Could not insert table type info into db: "
							+ e.getMessage());
		} finally {
			if (db != null)
				db.close();
		}
	}

}
